R Notebook

library(tidyverse)
library(scales)
library(plotly)
library(lubridate)
library(sf)
library(zoo)

Covid Tab

#load in beds and add year column
beds <- read_csv("raw_data/non_covid_raw_data/beds_by_nhs_board_of_treatment_and_specialty.csv") %>% janitor::clean_names()

beds <- beds %>% 
mutate(quarter_date = yq(quarter),
       year = year(quarter_date), 
       .after = quarter)

beds %>% 
write_csv("clean_data/non_covid_data/beds.csv")

Plot for bed availability for all acute

# bed percentage availablity for "all acute"
# Will need to add filter for year based on user input
beds_plotly <- beds %>%
  filter(specialty_name == "All Acute") %>% 
  group_by(quarter, specialty_name) %>%
  summarise(mean_perc_occ = mean(percentage_occupancy)) %>% 
  ggplot(aes(x = quarter, y = mean_perc_occ))+
  geom_line(aes(colour = specialty_name, group = specialty_name))+
  geom_point(aes(
    text = paste0("Occupancy:",round(mean_perc_occ, digits = 2),"%<br>","Date: ", quarter)),
    size = 0.5)+
  theme(axis.text.x = element_text(angle = 90, hjust = 1))+
  labs(title = "Mean bed availability for all Acute Patients",
       x = "\nYear and Quarter",
       y = "Average Percentage Occupancy")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.Warning: Ignoring unknown aesthetics: text

ggplotly(beds_plotly, tooltip = "text") %>% 
  config(displayModeBar = FALSE) %>% 
  layout(hoverlabel=list(bgcolor="red"))

NA
NA

Scotland Shapefile

ae_wait_times wrangling

ae_wait_times <- read_csv("raw_data/non_covid_raw_data/monthly_ae_waitingtimes_202206.csv") %>% janitor::clean_names()

Rows: 15837 Columns: 25── Column specification ────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (13): Country, HBT, TreatmentLocation, DepartmentType, NumberOfAttendancesEpisodeQF, Num...
dbl (12): Month, NumberOfAttendancesAggregate, NumberOfAttendancesEpisode, NumberMeetingTarg...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

#make a date and year column with the first date of every month
ae_wait_times <- ae_wait_times %>% 
  mutate(date = ym(month), .after = month,
         year = year(date))

#make a percent column with percent of patients meeting the 4hr target time
ae_wait_times <- ae_wait_times %>% 
  mutate(percent_4hr_target_achieved = (number_meeting_target_aggregate/number_of_attendances_aggregate)*100)

ae_wait_times %>% 
write_csv("clean_data/non_covid_data/ae_wait_times.csv")

add in target data for the shape files

#write in the target data for the shapefile colours
target_2007 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2007) %>% 
  rename(ae_target_2007 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2007)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

  

target_2008 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>%
  filter(year == 2008) %>% 
  rename(ae_target_2008 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2008)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2009 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2009) %>% 
  rename(ae_target_2009 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2009)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2010 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2010) %>% 
  rename(ae_target_2010 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2010)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2011 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2011) %>% 
  rename(ae_target_2011 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2011)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2012 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2012) %>% 
  rename(ae_target_2012 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2012)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2013 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2013) %>% 
  rename(ae_target_2013 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2013)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2014 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved =
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2014) %>% 
  rename(ae_target_2014 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2014)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2015 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2015) %>% 
  rename(ae_target_2015 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2015)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2016 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2016) %>% 
  rename(ae_target_2016 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2016)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2017 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2017) %>% 
  rename(ae_target_2017 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2017)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2018 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2018) %>% 
  rename(ae_target_2018 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2018)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2019 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2019) %>% 
  rename(ae_target_2019 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2019)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2020 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2020) %>% 
  rename(ae_target_2020 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2020)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2021 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = 
              round(mean(percent_4hr_target_achieved, na.rm = TRUE), digits = 2)) %>% 
  filter(year == 2021) %>% 
  rename(ae_target_2021 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2021)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

shape file wrangling

centres <-  scotland_smaller %>% 
  mutate(centres = round(st_centroid(st_make_valid(geometry))), digits = 4) %>%
    mutate(lat = round(st_coordinates(centres)[,1], digits = 4),
           long = round(st_coordinates(centres)[,2], digits = 4))

Error in `stopifnot()`:
! Problem while computing `centres = round(st_centroid(st_make_valid(geometry)))`.
Caused by error in `round()`:
! non-numeric argument to mathematical function
Backtrace:
  1. ... %>% ...
  8. dplyr:::mutate.data.frame(., centres = round(st_centroid(st_make_valid(geometry))), digits = 4)
  9. dplyr:::mutate_cols(.data, dplyr_quosures(...), caller_env = caller_env())
 11. mask$eval_all_mutate(quo)

SIMD graph

simd <- read_csv("raw_data/non_covid_raw_data/inpatient_and_daycase_by_nhs_board_of_treatment_and_simd.csv") %>% janitor::clean_names()

Rows: 40821 Columns: 18── Column specification ────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (11): Quarter, QuarterQF, HB, HBQF, Location, LocationQF, AdmissionType, AdmissionTypeQF...
dbl  (7): SIMD, Episodes, LengthOfEpisode, AverageLengthOfEpisode, Stays, LengthOfStay, Aver...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

simd <- simd %>% 
mutate(quarter_date = yq(quarter),
       year = year(quarter_date), 
       .after = quarter)

simd %>% 
write_csv("clean_data/non_covid_data/simd.csv")

# average episodes by SIMD value
# currently unfiltered for health board or admission type etc
simd_plotly <- simd %>% 
  drop_na(simd) %>%
  mutate(simd = as.factor(simd)) %>% # gives each simd a separate colour
  group_by(quarter, simd) %>% 
  summarise(avg_episodes = mean(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_episodes, group = simd))+
  geom_line(aes(colour = simd))+
  geom_point(aes(text = paste0("Date: ", quarter, "<br>",
                               "Average Episodes: ", round(avg_episodes, digits = 2), "<br>",
                               "SIMD: ", simd),
                   colour = simd),size = 0.5)+
  scale_y_continuous(labels = scales::comma)+
  labs(title = "Average Hospital Episodes by SIMD Deprevation score\n",
       x = "\nYear and Quarter",
       y = "Average Episodes\n")+
  theme_minimal()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.Warning: Ignoring unknown aesthetics: text

ggplotly(simd_plotly, tooltip = "text") %>% 
  config(displayModeBar = FALSE) 

NA
NA

Age Graph

age_sex <- read_csv("raw_data/non_covid_raw_data/inpatient_and_daycase_by_nhs_board_of_treatment_age_and_sex.csv") %>% janitor::clean_names()

Rows: 129393 Columns: 18── Column specification ────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (12): Quarter, QuarterQF, HB, HBQF, Location, LocationQF, AdmissionType, AdmissionTypeQF...
dbl  (6): Episodes, LengthOfEpisode, AverageLengthOfEpisode, Stays, LengthOfStay, AverageLen...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

age_sex <- age_sex %>%
    mutate(quarter_date = yq(quarter),
           year = year(quarter_date),
           .after = quarter)

age_sex %>% 
  write_csv("clean_data/non_covid_data/age_sex.csv")

# Average number of episode for age groups
# currently unfiltered by department or anything else
age_plotly <- age_sex %>% 
  #filter(min_date < year & year < max_date) %>% 
  group_by(quarter, age) %>% 
  summarise(avg_episodes = mean(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_episodes))+
  geom_line(aes(colour = age, group = age))+ 
  geom_point(aes(colour = age,
                 text = paste0("Date: ", quarter, "<br>",
                               "Average Episodes: ", round(avg_episodes, digits = 2), "<br>",
                               "Age Group: ", age)),size = 0.5)+
  labs(title = "Average Hospital Episodes by Age Groups\n",
       x = "\nYear and Quarter",
       y = "Average Episodes\n")+
  theme_minimal()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.Warning: Ignoring unknown aesthetics: text

  
ggplotly(age_plotly, tooltip = "text") %>% 
  config(displayModeBar = FALSE)

Gender

sex_plotly <- age_sex %>% 
  group_by(quarter, sex) %>% 
  summarise(avg_length_of_episode = mean(average_length_of_episode, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_episode))+
  geom_line(aes(colour = sex, group = sex))+
  geom_point(aes(colour = sex, 
                 text = paste0("Date: ", quarter, "<br>", 
                               "Gender: ", sex)),
                 size = 0.5)+
  labs(title = "Average Hospital Episodes by Gender\n",
       x = "\nYear and Quarter",
       y = "Average Episodes\n")+
  theme_minimal()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.Warning: Ignoring unknown aesthetics: text

  

ggplotly(sex_plotly, tooltip = "text") %>% 
  config(displayModeBar = FALSE)

A&E Tab

#THE A&E WAI TTIMES IS HANDLED ABOVE IN THE SHAPEFILE WRANGLING SECTION

#read the geo package in
scotland = st_read("clean_data/shapefile/scotland_smaller.gpkg")

Reading layer `scotland_smaller' from data source 
  `C:\Users\neilp\Documents\CODECLAN\phs_scotland_group_project\phs_scotland_group_project\clean_data\shapefile\scotland_smaller.gpkg' 
  using driver `GPKG'
Simple feature collection with 14 features and 19 fields
Geometry type: MULTIPOLYGON
Dimension:     XY
Bounding box:  xmin: 5512.998 ymin: 530635.8 xmax: 470290.5 ymax: 1220302
Projected CRS: OSGB 1936 / British National Grid